package com.css.fxfzxczh.modules.buildingKilometerGrid.repository;

import com.css.fxfzxczh.modules.buildingKilometerGrid.model.entity.FxqhBuildingGridRuralBottomFrame;
import com.css.fxfzxczh.modules.buildingKilometerGrid.model.param.QueryByVersionParam;

import com.css.fxfzxczh.modules.buildingKilometerGrid.model.vo.Grid13VO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;

/**
 * 农村底部框架结构房屋格网汇总数据结构表
 */
@Repository
@Slf4j
public class FxqhBuildingGridRuralBottomFrameRepository {
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 批量新增
     *
     * @param fxqhBuildingGridRuralBottomFrames
     */
    public void batchSave(List<FxqhBuildingGridRuralBottomFrame> fxqhBuildingGridRuralBottomFrames) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(fxqhBuildingGridRuralBottomFrames.toArray());
        String sql = "INSERT INTO fxqh_building_grid_rural_bottom_frame(id, total_area, housecount, residence, office, business, medic, other, y1980, y1981_y1990, y1991_y2000, y2001_y2010, y2011_y2015, y2016, f1, f2_f3, f4_f6, f7_f10, f11_f16, f17_f30, f31, kzsf0, kzsf1, kzsf2, kzsf3, kzsf4, profes, reinforce, source_flag, del_flag, data_version, create_user, create_time, update_user, update_time, geom, objectid, oid1, grid_rows, colums, area_code, shape_leng, shape_le1, shape_area, province, city, county, town, extends1, extends2, extends3, extends4, extends5, extends6, extends7, extends8, extends9, extends10, province_code, city_code, county_code, town_code) values(:id, :totalArea, :housecount, :residence, :office, :business, :medic, :other, :y1980, :y1981Y1990, :y1991Y2000, :y2001Y2010, :y2011Y2015, :y2016, :f1, :f2F3, :f4F6, :f7F10, :f11F16, :f17F30, :f31, :kzsf0, :kzsf1, :kzsf2, :kzsf3, :kzsf4, :profes, :reinforce, :sourceFlag, :delFlag, :dataVersion, :createUser, :createTime, :updateUser, :updateTime, ST_GeometryFromText(:geom, 4490), :objectid, :oid1, :gridRows, :colums, :areaCode, :shapeLeng, :shapeLe1, :shapeArea, :province, :city, :county, :town, :extends1, :extends2, :extends3, :extends4, :extends5, :extends6, :extends7, :extends8, :extends9, :extends10, :provinceCode, :cityCode, :countyCode, :townCode)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    /**
     * 临时数据转为真实数据
     *
     * @param sourceVersionCode
     * @param targetVersionCode
     */
    public void changeTempToRealData(String sourceVersionCode, String targetVersionCode) {
        String sql = " update fxqh_building_grid_rural_bottom_frame set data_version = '" + targetVersionCode + "' where data_version = '" + sourceVersionCode + "'";
        jdbcTemplate.update(sql);
    }

    public FxqhBuildingGridRuralBottomFrame getById(String id) {
        String sql = "select * from fxqh_building_grid_rural_bottom_frame where id = '" + id + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(FxqhBuildingGridRuralBottomFrame.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public void delByVersionCode(String versionCode, Date currTime, String currUid) {
        String sql = " update fxqh_building_grid_rural_bottom_frame set del_flag = '1', update_time = '" + currTime + "', update_user = '" + currUid + "' where data_version = '" + versionCode + "' and del_flag = '0'";
        jdbcTemplate.update(sql);
    }

    public List<FxqhBuildingGridRuralBottomFrame> listByDataVersion(QueryByVersionParam param) {
        String sql = "select *,ST_AsText(geom) geom from fxqh_building_grid_rural_bottom_frame where del_flag = '0' and data_version = '" + param.getVersionCode() + "'";
        sql = queryByDataVersionSQLBuild(param, sql);
        sql += " order by create_time desc ";
        if (param.getPage() != null && param.getRows() != null) {
            sql += (" limit " + param.getRows() + " offset " + param.getRows() * (Math.max(param.getPage(), 1) - 1));
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FxqhBuildingGridRuralBottomFrame.class));
    }

    private static String queryByDataVersionSQLBuild(QueryByVersionParam queryByVersionParam, String sql) {
        if (StringUtils.isNotBlank(queryByVersionParam.getProvince())) {
            sql += " and province = '" + queryByVersionParam.getProvince() + "' ";
        }
        if (StringUtils.isNotBlank(queryByVersionParam.getCity())) {
            sql += " and city = '" + queryByVersionParam.getCity() + "' ";
        }
        if (StringUtils.isNotBlank(queryByVersionParam.getCounty())) {
            sql += " and county = '" + queryByVersionParam.getCounty() + "' ";
        }
        if (StringUtils.isNotBlank(queryByVersionParam.getAreaCode())) {
            sql += " and county_code = '" + queryByVersionParam.getAreaCode() + "' ";
        }
        if (StringUtils.isNotBlank(queryByVersionParam.getExtends1())) {
            sql += " and extends1='" + queryByVersionParam.getExtends1() + "'";
        } else if (StringUtils.isNotBlank(queryByVersionParam.getGeom())) {
            sql += " and ST_Intersects (geom, st_geomFromText('" + queryByVersionParam.getGeom() + "',4490) )";
        }

//        if (StringUtils.isNotBlank(queryByVersionParam.getKeyword())) {
//            sql += " and (jb_code ='" + queryByVersionParam.getKeyword() + "' or sj_name like'%" + queryByVersionParam.getKeyword() + "%' or province like'%" + queryByVersionParam.getKeyword() + "%' or city like'%" + queryByVersionParam.getKeyword() + "%' or county like'%" + queryByVersionParam.getKeyword() + "%' or town like'%" + queryByVersionParam.getKeyword() + "%' )";
//        }
        return sql;
    }

    public Long countByDataVersion(QueryByVersionParam param) {
        String sql = "select count(*) from fxqh_building_grid_rural_bottom_frame where del_flag = '0' and data_version = '" + param.getVersionCode() + "'";
        sql = queryByDataVersionSQLBuild(param, sql);
        return jdbcTemplate.queryForObject(sql, Long.class);
    }

    public List<FxqhBuildingGridRuralBottomFrame> listGridDataByVersionCode(String version) {
        String sql = "select *,ST_AsText(geom) geom from fxqh_building_grid_rural_bottom_frame where del_flag = '0' and data_version = '" + version + "'";
        sql += " order by create_time desc ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(FxqhBuildingGridRuralBottomFrame.class));
    }

    public List<Grid13VO> queryDataByParam(QueryByVersionParam param) {
        StringBuilder sql = new StringBuilder("SELECT province,city,county,total_area totalArea,residence,residence,office,business,medic,other,kzsf0 sfIntensity,kzsf1 sfIntensity6," +
                "kzsf2 sfIntensity7,kzsf3 sfIntensity8,kzsf4 sfIntensity9,ST_AsText(geom) geom,'农村底部框架结构' as structureType,extends1 FROM (select * from fxqh_building_grid_rural_bottom_frame where del_flag = '0' ");
        if (param.getGeomType() != null && 1 == param.getGeomType()) {
            sql.append(" and st_intersects(geom,st_geomFromText('" + param.getGeom() + "',4490))");
        } else {
            if (StringUtils.isNotBlank(param.getProvince())) {
                sql.append(" and province = '" + param.getProvince() + "' ");
            }
            if (StringUtils.isNotBlank(param.getCity())) {
                sql.append(" and city = '" + param.getCity() + "' ");
            }
            if (StringUtils.isNotBlank(param.getCounty())) {
                sql.append(" and county = '" + param.getCounty() + "' ");
            }
        }
        if (StringUtils.isNotBlank(param.getVersionCode())) {
            sql.append(" and county is not null and data_version = '" + param.getVersionCode() + "') t ");
        }
        sql.append("WHERE ST_Intersects(t.geom,(select a.geom FROM fxqh_setting_seismic_influenceflied a WHERE a.id = '" + param.getField() + "')) ");
        sql.append(" order by county  desc ");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Grid13VO.class));
    }

    public List<Grid13VO> queryDataByParam1(QueryByVersionParam param) {
        String sql = "select id,province,city,county,total_area totalArea,residence,office,business,medic,other,kzsf0 sfIntensity,kzsf1 sfIntensity6," +
                "kzsf2 sfIntensity7,kzsf3 sfIntensity8,kzsf4 sfIntensity9,ST_AsText(st_intersection(a.geom, b.geom)) geom,'农村底部框架结构' as structureType," +
                "extends1,st_area(st_intersection(a.geom, b.geom)) area,b.dz_intensity intensity" +
                " from (select * from fxqh_building_grid_rural_bottom_frame where del_flag = '0' and data_version = '" + param.getVersionCode() + "'";
        if (param.getGeomType() != null && 1 == param.getGeomType()) {
            sql = sql + " and st_intersects(geom,st_geomFromText('" + param.getGeom() + "',4490))";
        } else {
            sql = queryByDataVersionSQLBuild(param, sql);
        }
        sql = sql + ") a,(select dz_intensity,geom from fxqh_setting_seismic_influenceflied where version_code = '" + param.getInfluenceVersion() + "' and del_flag='0') b" +
                " where  st_intersects(a.geom,b.geom)";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Grid13VO.class));
    }

    public List<Grid13VO> queryYwData(QueryByVersionParam param) {
        String sql = "select id,province,city,county,total_area totalArea,residence,residence,office,business,medic,other,kzsf0 sfIntensity,kzsf1 sfIntensity6," +
                "kzsf2 sfIntensity7,kzsf3 sfIntensity8,kzsf4 sfIntensity9,ST_AsText(geom) geom,'农村底部框架结构' as structureType ,extends1 " +
                " from fxqh_building_grid_rural_bottom_frame where del_flag = '0' and county is not null and data_version = '" + param.getVersionCode() + "'";
        if (param.getGeomType() != null && 1 == param.getGeomType()) {
            sql = sql + " and st_intersects(geom,st_geomFromText('" + param.getGeom() + "',4490))";
        } else {
            sql = queryByDataVersionSQLBuild(param, sql);
        }
        //sql += " order by create_time desc ";
        if (param.getPage() != null && param.getRows() != null) {
            sql += (" limit " + param.getRows() + " offset " + param.getRows() * (Math.max(param.getPage(), 1) - 1));
        }
        sql += " order by county desc ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Grid13VO.class));
    }

    public List<Grid13VO> getJcld(QueryByVersionParam param) {
        StringBuilder sql = new StringBuilder("SELECT b.id,b.province,b.city,b.county,b.total_area totalArea,b.residence,b.residence,b.office,b.business,b.medic,b.other,b.kzsf0 sfIntensity,b.kzsf1 sfIntensity6,b.kzsf2 sfIntensity7,b.kzsf3 sfIntensity8,b.kzsf4 sfIntensity9,ST_AsText(b.geom) geom,b.extends1" +
                ",a.ld50year63 as year50ProExceedance063,a.ld50year10 as year50ProExceedance010,a.ld50year2 as year50ProExceedance002,a.ld100year1 as year100ProExceedance001,'农村底部框架结构' as structureType " +
                "from (select * from jc_ld where assess_id='" + param.getAssessId() + "') a " +
                "left join " +
                "(select * from fxqh_building_grid_rural_bottom_frame where 1=1");
        if (param.getGeomType() != null && 1 == param.getGeomType()) {
            sql.append(" and st_intersects(geom,st_geomFromText('" + param.getGeom() + "',4490))");
        } else {
            if (StringUtils.isNotBlank(param.getProvince())) {
                sql.append(" and province = '" + param.getProvince() + "' ");
            }
            if (StringUtils.isNotBlank(param.getCity())) {
                sql.append(" and city = '" + param.getCity() + "' ");
            }
            if (StringUtils.isNotBlank(param.getCounty())) {
                sql.append(" and county = '" + param.getCounty() + "' ");
            }
        }
        if (StringUtils.isNotBlank(param.getVersionCode())) {
            sql.append(" and county is not null and data_version = '" + param.getVersionCode() + "') b ");
        }
        sql.append(" on " +
                " a.extends1 = b.extends1");
        sql.append(" order by county  desc ");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Grid13VO.class));
    }

    public List<Grid13VO> getPga(QueryByVersionParam param) {
        StringBuilder sql = new StringBuilder("SELECT b.id,b.province,b.city,b.county,b.total_area totalArea,b.residence,b.residence,b.office,b.business,b.medic,b.other,b.kzsf0 sfIntensity,b.kzsf1 sfIntensity6,b.kzsf2 sfIntensity7,b.kzsf3 sfIntensity8,b.kzsf4 sfIntensity9,ST_AsText(b.geom) geom,b.extends1" +
                ",a.ld50year63 as year50ProExceedance063,a.ld50year10 as year50ProExceedance010,a.ld50year2 as year50ProExceedance002,a.ld100year1 as year100ProExceedance001,'农村底部框架结构' as structureType  " +
                "FROM (select * from jc_pga where assess_id='" + param.getAssessId() + "')a " +
                "left join " +
                "(select * from fxqh_building_grid_rural_bottom_frame where  1=1 ");
        if (param.getGeomType() != null && 1 == param.getGeomType()) {
            sql.append(" and st_intersects(geom,st_geomFromText('" + param.getGeom() + "',4490))");
        } else {
            if (StringUtils.isNotBlank(param.getProvince())) {
                sql.append(" and province = '" + param.getProvince() + "' ");
            }
            if (StringUtils.isNotBlank(param.getCity())) {
                sql.append(" and city = '" + param.getCity() + "' ");
            }
            if (StringUtils.isNotBlank(param.getCounty())) {
                sql.append(" and county = '" + param.getCounty() + "' ");
            }
        }
        if (StringUtils.isNotBlank(param.getVersionCode())) {
            sql.append(" and county is not null and data_version = '" + param.getVersionCode() + "') b ");
        }
        sql.append(" on " +
                " a.extends1 = b.extends1");
        sql.append(" order by county  desc ");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Grid13VO.class));
    }

    public void insert(FxqhBuildingGridRuralBottomFrame fxqhBuildingGridRuralBottomFrame) {
        String sql = "INSERT INTO fxqh_building_grid_rural_bottom_frame(id, total_area, housecount, residence, office, business, medic, other, y1980, y1981_y1990, y1991_y2000, y2001_y2010, y2011_y2015, y2016, f1, f2_f3, f4_f6, f7_f10, f11_f16, f17_f30, f31, kzsf0, kzsf1, kzsf2, kzsf3, kzsf4, profes, reinforce, source_flag, del_flag, data_version, create_user, create_time, update_user, update_time, geom, objectid, oid1, grid_rows, colums, area_code, shape_leng, shape_le1, shape_area, province, city, county, town, extends1, extends2, extends3, extends4, extends5, extends6, extends7, extends8, extends9, extends10, province_code, city_code, county_code, town_code) values(:id, :totalArea, :housecount, :residence, :office, :business, :medic, :other, :y1980, :y1981Y1990, :y1991Y2000, :y2001Y2010, :y2011Y2015, :y2016, :f1, :f2F3, :f4F6, :f7F10, :f11F16, :f17F30, :f31, :kzsf0, :kzsf1, :kzsf2, :kzsf3, :kzsf4, :profes, :reinforce, :sourceFlag, :delFlag, :dataVersion, :createUser, :createTime, :updateUser, :updateTime, ST_GeometryFromText(:geom, 4490), :objectid, :oid1, :gridRows, :colums, :areaCode, :shapeLeng, :shapeLe1, :shapeArea, :province, :city, :county, :town, :extends1, :extends2, :extends3, :extends4, :extends5, :extends6, :extends7, :extends8, :extends9, :extends10, :provinceCode, :cityCode, :countyCode, :townCode)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(fxqhBuildingGridRuralBottomFrame);
        namedParameterJdbcTemplate.update(sql, source);
    }
}
